create database if not exists epoms;
use epoms ;



create table if not exists  emp_mgmt(emp_id int,own_date varchar(20) ,prod_id int);
insert into emp_mgmt values(102,"2009-12-12",1001);

create table if not exists prod_mgmt(prod_id int primary key ,emp_id int );
insert into prod_mgmt values(1001,102);


create table if not exists prod(prod_id int primary key ,prod_name varchar(20),prod_descp varchar(20));
insert into prod values(1002,"laptop","mobile device");


DELIMITER //
create procedure   ins_prod(IN uprod_id int,IN uprod_name varchar(20),IN uprod_descp varchar(20))
BEGIN
      INSERT INTO prod values (uprod_id,uprod_name,uprod_descp);
END //

call ins_prod(1001,"Cell-Phone","Hand -Held Device");


DELIMITER //
create procedure   del_prod(IN uprod_id int)
BEGIN
       DELETE FROM  prod where prod_id = uprod_id;
END //

call del_prod(1001);


DELIMITER //
create procedure   upd_prod(IN uprod_id int,IN uprod_name varchar(20),IN uprod_descp varchar(20))

BEGIN
       UPDATE  prod SET PROD_ID = uprod_id,prod_name=uprod_name,prod_descp=uprod_descp  where  prod_id = uprod_id;
END //

call upd_prod(112,"IPOD","HH Device");


DELIMITER //
create procedure   query_prod(IN uemp_id int)

BEGIN
  
   select prod.prod_id,prod.prod_name,prod.prod_descp,prod_mgmt.emp_id from prod,prod_mgmt where prod_mgmt.emp_id = uemp_id;       

END//

call query_prod(112);



